const { testConnection, query } = require('./lib/db');

async function testDatabaseOperations() {
  console.log('🔗 测试数据库连接...');
  
  try {
    // 测试连接
    const isConnected = await testConnection();
    if (!isConnected) {
      console.error('❌ 数据库连接失败');
      return;
    }
    
    // 测试基本查询
    console.log('\n📊 测试基本查询操作...');
    
    // 测试用户查询
    const users = await query('SELECT id, username, name, role FROM users LIMIT 3');
    console.log('✅ 用户查询成功:', users.length, '条记录');
    users.forEach(user => {
      console.log(`  - ${user.username} (${user.name}) - ${user.role}`);
    });
    
    // 测试工单查询
    const tickets = await query('SELECT id, ticket_number, title, status FROM tickets');
    console.log('\n✅ 工单查询成功:', tickets.length, '条记录');
    tickets.forEach(ticket => {
      console.log(`  - ${ticket.ticket_number}: ${ticket.title} [${ticket.status}]`);
    });
    
    // 测试应用查询
    const applications = await query('SELECT id, code, name FROM applications LIMIT 5');
    console.log('\n✅ 应用查询成功:', applications.length, '条记录');
    applications.forEach(app => {
      console.log(`  - ${app.code}: ${app.name}`);
    });
    
    // 测试复杂连接查询
    const ticketDetails = await query(`
      SELECT 
        t.ticket_number, 
        t.title, 
        t.status, 
        u1.name as creator_name,
        u2.name as support_staff_name,
        a.name as application_name
      FROM tickets t
      LEFT JOIN users u1 ON t.user_id = u1.id
      LEFT JOIN users u2 ON t.support_staff_id = u2.id
      LEFT JOIN applications a ON t.application_id = a.id
      LIMIT 3
    `);
    console.log('\n✅ 复杂连接查询成功:', ticketDetails.length, '条记录');
    ticketDetails.forEach(ticket => {
      console.log(`  - ${ticket.ticket_number}: ${ticket.title}`);
      console.log(`    创建人: ${ticket.creator_name}`);
      console.log(`    支持人员: ${ticket.support_staff_name || '未分配'}`);
      console.log(`    应用: ${ticket.application_name || '未指定'}`);
    });
    
    // 测试工作流状态转换规则查询
    const transitions = await query(`
      SELECT from_status, to_status, required_role 
      FROM ticket_status_transitions 
      WHERE required_role = 'user' 
      LIMIT 5
    `);
    console.log('\n✅ 状态转换规则查询成功:', transitions.length, '条记录');
    transitions.forEach(rule => {
      console.log(`  - ${rule.from_status} → ${rule.to_status} (需要: ${rule.required_role})`);
    });
    
    console.log('\n🎉 所有数据库操作测试成功！');
    console.log('\n📋 数据库总结:');
    console.log('  ✓ 数据库连接正常');
    console.log('  ✓ 基础表结构完整');
    console.log('  ✓ 示例数据已就绪');
    console.log('  ✓ 外键关系正常');
    console.log('  ✓ 复杂查询可执行');
    console.log('  ✓ 工作流规则已配置');
    
  } catch (error) {
    console.error('❌ 数据库操作测试失败:', error.message);
    console.error(error);
  }
}

// 执行测试
testDatabaseOperations();